package com.example.mapp;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;

import com.example.entity.ElevatorAppraisal;
import com.example.entity.Promotion;
import com.example.entity.PromotionParty;
import com.example.entity.PromotionPartyAdded;
import com.example.entity.PromotionRecord;
import com.example.entity.SecurityTipRecord;
import com.example.ext.entity.ExtPromotionRecord;
import com.example.mapp.buildSql.PromotionMappSql;

/**
 * Created by douyx on 2018/03/18.
 */
@Mapper
public interface PromotionMapp {

	@Insert(" insert into promotion (user_id,type,grade,empiric_value,achieve) value (#{userId},#{type},#{grade},#{empiricValue},#{achieve})")
	@SelectKey(statement = "select last_insert_id()", keyProperty = "id", keyColumn = "id", before = false, resultType = Long.class)
	int insert(Promotion promotion) throws Exception;

	@Insert(" insert into promotion_record (user_id,type,added_time,empiric_item,empiric_value) value (#{userId},#{type},#{addedTime},#{empiricItem},#{empiricValue})")
	@SelectKey(statement = "select last_insert_id()", keyProperty = "id", keyColumn = "id", before = false, resultType = Long.class)
	int insertRecord(PromotionRecord promotionRecord) throws Exception;

    @SelectProvider(
            type = PromotionMappSql.class,
            method = "findRecord"
    )
    List<PromotionRecord> findRecord(ExtPromotionRecord promotionRecord) throws Exception;

	@Update(" update promotion set grade=#{grade},empiric_value=#{empiricValue},achieve=#{achieve} where id=#{id}")
	int update(Promotion promotion) throws Exception;

	@Select(" select * from promotion where user_id = #{userId} and type = #{type}")
	Promotion findById(Map<String, Object> map) throws Exception;

	@Insert(" insert into promotion_party (user_id,type,corporation,telephone,contacts,contacts_work,contacts_card,contacts_phone,"
			+ "contacts_emile,contacts_mobile,contacts_address,zzjb) value (#{userId},#{type},#{corporation},#{telephone},#{contacts},"
			+ "#{contactsWork},#{contactsCard},#{contactsPhone},#{contactsEmile},#{contactsMobile},#{contactsAddress},#{zzjb})")
	@SelectKey(statement = "select last_insert_id()", keyProperty = "id", keyColumn = "id", before = false, resultType = Long.class)
	int insertParty(PromotionParty promotionParty) throws Exception;

	@Update(" update promotion_party set corporation=#{corporation},telephone=#{telephone},contacts=#{contacts},contacts_work=#{contactsWork},"
			+ "contacts_card=#{contactsCard},contacts_phone=#{contactsPhone},contacts_emile=#{contactsEmile},contacts_mobile=#{contactsMobile},"
			+ "contacts_address=#{contactsAddress},zzjb=#{zzjb} where id=#{id}")
	int updateParty(PromotionParty promotionParty) throws Exception;

	@Select(" select * from promotion_party where user_id = #{id}")
	PromotionParty findPartyById(Long id) throws Exception;

	@Insert(" insert into promotion_party_added (user_id,month,empiric_value_added) value (#{userId},#{month},#{empiricValueAdded})")
	@SelectKey(statement = "select last_insert_id()", keyProperty = "id", keyColumn = "id", before = false, resultType = Long.class)
	int insertPartyAdded(PromotionPartyAdded promotionPartyAdded) throws Exception;

	@Update(" update promotion_party_added set empiric_value_added=#{empiricValueAdded} where id=#{id}")
	int updatePartyAdded(PromotionPartyAdded promotionPartyAdded) throws Exception;

	@Select(" select * from promotion_party_added where user_id = #{userId} and month = #{month} ")
	PromotionPartyAdded findPartyAddedById(Map<String, Object> map) throws Exception;

	@Insert(" INSERT INTO PROMOTION_PARTY_SCORE (ID,PARTY_TYPE,ADD_SCORE) "
			+ "SELECT id,party_type,ADD_SCROE FROM ( "
			+ "SELECT ELE.ID,ELE.PARTY_TYPE, "
			+ "CASE WHEN USER.USER_COUNT IS NULL OR USER.USER_COUNT = 0 THEN 30*1000 ELSE ROUND((30 - ELE.ELE_COUNT/USER.USER_COUNT)*1000) END ADD_SCROE FROM "
			+ "( "
			+ "SELECT P.id,P.PARTY_TYPE,COUNT(1) ELE_COUNT FROM elevator e  "
			+ "INNER JOIN party p ON e.maintenance_party_id=p.id "
			+ "WHERE p.party_type='1' "
			+ "GROUP BY P.ID,P.PARTY_TYPE "
			+ ") ELE "
			+ "LEFT JOIN ( "
			+ "SELECT P.id,P.PARTY_TYPE,COUNT(1) USER_COUNT FROM PARTY P "
			+ "INNER JOIN PARTY_USER PU ON P.id=PU.party_id "
			+ "INNER JOIN sys_user_role SUR ON PU.user_id=SUR.user_id "
			+ "INNER JOIN system_role SR ON SUR.role_id=SR.id "
			+ "WHERE P.party_type = '1' "
			+ "AND SR.role_name='电梯维保人员' "
			+ "GROUP BY P.id,P.party_type "
			+ ") USER ON ELE.ID=USER.ID AND ELE.PARTY_TYPE=USER.PARTY_TYPE "
			+ "UNION "
			+ "SELECT ELE.ID,ELE.PARTY_TYPE, "
			+ "CASE WHEN USER.USER_COUNT IS NULL OR USER.USER_COUNT = 0 THEN 50*1000 ELSE ROUND((50 - ELE.ELE_COUNT/USER.USER_COUNT)*1000) END ADD_SCROE FROM "
			+ "( "
			+ "SELECT P.id,P.PARTY_TYPE,COUNT(1) ELE_COUNT FROM elevator e  "
			+ "INNER JOIN party p ON e.used_party_id=p.id "
			+ "WHERE p.party_type='2' "
			+ "GROUP BY P.ID,P.PARTY_TYPE "
			+ ") ELE "
			+ "LEFT JOIN ( "
			+ "SELECT P.id,P.PARTY_TYPE,COUNT(1) USER_COUNT FROM PARTY P "
			+ "INNER JOIN PARTY_USER PU ON P.id=PU.party_id "
			+ "INNER JOIN sys_user_role SUR ON PU.user_id=SUR.user_id "
			+ "INNER JOIN system_role SR ON SUR.role_id=SR.id "
			+ "WHERE P.party_type = '2' "
			+ "AND SR.role_name='电梯安全管理员' "
			+ "GROUP BY P.id,P.party_type "
			+ ") USER ON ELE.ID=USER.ID AND ELE.PARTY_TYPE=USER.PARTY_TYPE) A ")
	void insertPartyAddedScore() throws Exception;

	@Update(" UPDATE PROMOTION P,PROMOTION_PARTY_SCORE SCORE "
			+ "SET P.EMPIRIC_VALUE = P.EMPIRIC_VALUE - ( CASE WHEN P.LAST_ADDED IS NULL THEN 0 ELSE P.LAST_ADDED END ) + SCORE.ADD_SCORE,P.LAST_ADDED=SCORE.ADD_SCORE  "
			+ "WHERE P.USER_ID=SCORE.ID AND P.TYPE=SCORE.PARTY_TYPE ")
	void updatePartyAddedScore() throws Exception;

	@Delete(" delete from PROMOTION_PARTY_SCORE")
	void deletePartyAddedScore() throws Exception;

	@Insert("INSERT INTO PROMOTION_PARTY_ADDED (MONTH,user_id,empiric_value_added) "
			+ "SELECT "
			+ "#{month},p.user_id, SCORE.ADD_SCORE - ( CASE WHEN P.LAST_ADDED IS NULL THEN 0 ELSE P.LAST_ADDED END ) FROM "
			+ "PROMOTION P,PROMOTION_PARTY_SCORE SCORE "
			+ "WHERE P.USER_ID=SCORE.ID AND p.type=score.PARTY_TYPE "
			+ "AND SCORE.ID NOT IN ("
			+ "SELECT P.USER_ID FROM PROMOTION P,PROMOTION_PARTY_SCORE SCORE1,PROMOTION_PARTY_ADDED added "
			+ "WHERE P.USER_ID=SCORE1.ID AND p.type=score1.PARTY_TYPE AND added.user_id=P.user_id AND added.month=#{month})")
	void insertPartyAdded2(String month) throws Exception;

	@Update("UPDATE PROMOTION_PARTY_ADDED ADDED,PROMOTION P,PROMOTION_PARTY_SCORE SCORE "
			+ "SET ADDED.empiric_value_added = ADDED.empiric_value_added + SCORE.ADD_SCORE - ( CASE WHEN P.LAST_ADDED IS NULL THEN 0 ELSE P.LAST_ADDED END ) "
			+ "WHERE P.USER_ID=SCORE.ID AND p.type=score.PARTY_TYPE AND ADDED.user_id=P.user_id AND added.month = #{month}")
	void updatePartyAdded2(String month) throws Exception;

	@Insert("INSERT INTO promotion_record (user_id,type,added_time,empiric_item,empiric_value) "
			+ "SELECT "
			+ "p.user_id, P.TYPE, DATE_FORMAT(NOW(),'%Y%m%d%H%i%S'),"
			+ "CASE WHEN P.TYPE = '1' THEN '维保人员与设备数量匹配程度' ELSE '电梯安全管理员与设备数量匹配程度' END,"
			+ "SCORE.ADD_SCORE - ( CASE WHEN P.LAST_ADDED IS NULL THEN 0 ELSE P.LAST_ADDED END ) FROM "
			+ "PROMOTION P,PROMOTION_PARTY_SCORE SCORE "
			+ "WHERE P.USER_ID=SCORE.ID AND p.type=score.PARTY_TYPE ")
	void insertPartyAdded3() throws Exception;

	@Select(" SELECT obj_new.user_name,obj_new.party_name,obj_new.empiric_value,obj_new.achieve,obj_new.grade, "
			+ "obj_new.rownum ranking "
			+ "FROM "
			+ "( "
			+ "SELECT obj.user_name,obj.party_name,obj.empiric_value,obj.achieve,obj.grade, "
			+ "@rownum := @rownum + 1 AS num_tmp, "
			+ "@incrnum := CASE "
			+ "WHEN @rowtotal = obj.empiric_value THEN "
			+ "@incrnum "
			+ "WHEN @rowtotal := obj.empiric_value THEN "
			+ "@rownum "
			+ "END AS rownum "
			+ "FROM "
			+ "( "
			+ "SELECT '' user_name,p1.party_name,p2.empiric_value,p2.achieve,p2.grade FROM party p1  "
			+ "INNER JOIN promotion p2 ON p1.id=p2.user_id AND p1.party_type=p2.type "
			+ "WHERE p1.parent_id = #{parentId} AND p1.party_type = #{type}  "
			+ "ORDER BY p2.empiric_value DESC "
			+ ") AS obj, "
			+ "( "
			+ "SELECT "
			+ "@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 "
			+ ") r "
			+ ") AS obj_new LIMIT 10 ")
	List<Map<String, Object>> findRankingPartyList(Map<String, Object> map) throws Exception;

	@Select(" SELECT obj_new.user_name,obj_new.party_name,obj_new.empiric_value,obj_new.achieve,obj_new.grade, "
			+ "obj_new.rownum ranking "
			+ "FROM "
			+ "( "
			+ "SELECT obj.user_name,obj.party_name,obj.empiric_value,obj.achieve,obj.grade, "
			+ "@rownum := @rownum + 1 AS num_tmp, "
			+ "@incrnum := CASE "
			+ "WHEN @rowtotal = obj.empiric_value THEN "
			+ "@incrnum "
			+ "WHEN @rowtotal := obj.empiric_value THEN "
			+ "@rownum "
			+ "END AS rownum "
			+ "FROM "
			+ "( "
			+ "SELECT '' user_name,p1.party_name,p2.empiric_value,p2.achieve,p2.grade FROM party p1  "
			+ "INNER JOIN promotion p2 ON p1.id=p2.user_id AND p1.party_type=p2.type "
			+ "WHERE p1.party_type = #{type}  "
			+ "ORDER BY p2.empiric_value DESC "
			+ ") AS obj, "
			+ "( "
			+ "SELECT "
			+ "@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 "
			+ ") r "
			+ ") AS obj_new LIMIT 10 ")
	List<Map<String, Object>> findRanksPartyList(Map<String, Object> map) throws Exception;

	@Select(" SELECT obj_new.user_name,obj_new.party_name,obj_new.empiric_value,obj_new.achieve,obj_new.grade, "
			+ "obj_new.rownum ranking "
			+ "FROM "
			+ "( "
			+ "SELECT obj.user_name,obj.party_name,obj.empiric_value,obj.achieve,obj.grade, "
			+ "@rownum := @rownum + 1 AS num_tmp, "
			+ "@incrnum := CASE "
			+ "WHEN @rowtotal = obj.empiric_value THEN "
			+ "@incrnum "
			+ "WHEN @rowtotal := obj.empiric_value THEN "
			+ "@rownum "
			+ "END AS rownum "
			+ "FROM "
			+ "( "
			+ "SELECT p2.user_name,p4.party_name,p1.empiric_value,p1.achieve,p1.grade FROM promotion p1 "
			+ "INNER JOIN sys_user p2 ON p1.user_id=p2.id "
			+ "INNER JOIN party_user p3 ON p2.id=p3.user_id "
			+ "INNER JOIN party p4 ON p3.party_id=p4.id "
			+ "WHERE p1.type = #{type}  "
			+ "ORDER BY p1.empiric_value DESC "
			+ ") AS obj, "
			+ "( "
			+ "SELECT "
			+ "@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 "
			+ ") r "
			+ ") AS obj_new LIMIT 10 ")
	List<Map<String, Object>> findRanksUserList(Map<String, Object> map) throws Exception;

	@Select(" SELECT obj_new.user_name,obj_new.party_name,obj_new.empiric_value,obj_new.achieve,obj_new.grade, "
			+ "obj_new.rownum ranking "
			+ "FROM "
			+ "( "
			+ "SELECT obj.user_name,obj.party_name,obj.empiric_value,obj.achieve,obj.grade,obj.id, "
			+ "@rownum := @rownum + 1 AS num_tmp, "
			+ "@incrnum := CASE "
			+ "WHEN @rowtotal = obj.empiric_value THEN "
			+ "@incrnum "
			+ "WHEN @rowtotal := obj.empiric_value THEN "
			+ "@rownum "
			+ "END AS rownum "
			+ "FROM "
			+ "( "
			+ "SELECT '' user_name,p1.party_name,p2.empiric_value,p2.achieve,p2.grade,p1.id FROM party p1  "
			+ "INNER JOIN promotion p2 ON p1.id=p2.user_id AND p1.party_type=p2.type "
			+ "WHERE p1.parent_id = #{parentId} AND p1.party_type = #{type}  "
			+ "ORDER BY p2.empiric_value DESC "
			+ ") AS obj, "
			+ "( "
			+ "SELECT "
			+ "@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 "
			+ ") r "
			+ ") AS obj_new "
			+ "WHERE obj_new.id = #{userId}")
	Map<String, Object> findRankingParty(Map<String, Object> map) throws Exception;

	@Select(" SELECT obj_new.user_name,obj_new.party_name,obj_new.empiric_value,obj_new.achieve,obj_new.grade, "
			+ "obj_new.rownum ranking "
			+ "FROM "
			+ "( "
			+ "SELECT obj.user_name,obj.party_name,obj.empiric_value,obj.achieve,obj.grade, "
			+ "@rownum := @rownum + 1 AS num_tmp, "
			+ "@incrnum := CASE "
			+ "WHEN @rowtotal = obj.empiric_value THEN "
			+ "@incrnum "
			+ "WHEN @rowtotal := obj.empiric_value THEN "
			+ "@rownum "
			+ "END AS rownum "
			+ "FROM "
			+ "( "
			+ "SELECT p2.user_name,p4.party_name,p1.empiric_value,p1.achieve,p1.grade FROM promotion p1 "
			+ "INNER JOIN sys_user p2 ON p1.user_id=p2.id "
			+ "INNER JOIN party_user p3 ON p2.id=p3.user_id "
			+ "INNER JOIN party p4 ON p3.party_id=p4.id "
			+ "WHERE (p4.parent_id = #{parentId} OR p4.id = #{parentId}) AND p1.type = #{type}  "
			+ "ORDER BY p1.empiric_value DESC "
			+ ") AS obj, "
			+ "( "
			+ "SELECT "
			+ "@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 "
			+ ") r "
			+ ") AS obj_new LIMIT 10 ")
	List<Map<String, Object>> findRankingUserList(Map<String, Object> map) throws Exception;

	@Select(" SELECT obj_new.user_name,obj_new.party_name,obj_new.empiric_value,obj_new.achieve,obj_new.grade, "
			+ "obj_new.rownum ranking "
			+ "FROM "
			+ "( "
			+ "SELECT obj.user_name,obj.party_name,obj.empiric_value,obj.achieve,obj.grade,obj.user_id, "
			+ "@rownum := @rownum + 1 AS num_tmp, "
			+ "@incrnum := CASE "
			+ "WHEN @rowtotal = obj.empiric_value THEN "
			+ "@incrnum "
			+ "WHEN @rowtotal := obj.empiric_value THEN "
			+ "@rownum "
			+ "END AS rownum "
			+ "FROM "
			+ "( "
			+ "SELECT p2.user_name,p4.party_name,p1.empiric_value,p1.achieve,p1.grade,p1.user_id FROM promotion p1 "
			+ "INNER JOIN sys_user p2 ON p1.user_id=p2.id "
			+ "INNER JOIN party_user p3 ON p2.id=p3.user_id "
			+ "INNER JOIN party p4 ON p3.party_id=p4.id "
			+ "WHERE (p4.parent_id = #{parentId} OR p4.id = #{parentId}) AND p1.type = #{type}  "
			+ "ORDER BY p1.empiric_value DESC "
			+ ") AS obj, "
			+ "( "
			+ "SELECT "
			+ "@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 "
			+ ") r "
			+ ") AS obj_new "
			+ "WHERE obj_new.user_id = #{userId} ")
	Map<String, Object> findRankingUser(Map<String, Object> map) throws Exception;

	@Select("SELECT GROUP_CONCAT(obj_new.party_name,'（', obj_new.empiric_value, '）' SEPARATOR '、') content "
			+ "FROM "
			+ "( "
			+ "SELECT obj.party_name,obj.empiric_value, "
			+ "@rownum := @rownum + 1 AS num_tmp, "
			+ "@incrnum := CASE "
			+ "WHEN @rowtotal = obj.empiric_value THEN "
			+ "@incrnum "
			+ "WHEN @rowtotal := obj.empiric_value THEN "
			+ "@rownum "
			+ "END AS rownum "
			+ "FROM "
			+ "( "
			+ "SELECT p1.party_name,p2.empiric_value FROM party p1  "
			+ "INNER JOIN promotion p2 ON p1.id=p2.user_id AND p1.party_type=p2.type "
			+ "WHERE p1.party_type = #{type} "
			+ "ORDER BY p2.empiric_value DESC "
			+ ") AS obj, "
			+ "( "
			+ "SELECT "
			+ "@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 "
			+ ") r "
			+ ") AS obj_new "
			+ "WHERE obj_new.rownum=1 ")
	Map<String, Object> findRankingPartyOne(String type) throws Exception;

	@Select(" SELECT GROUP_CONCAT(obj_new.party_name,'（', obj_new.empiric_value_added, '）' SEPARATOR '、') content "
			+ "FROM "
			+ "( "
			+ "SELECT obj.party_name,obj.empiric_value_added, "
			+ "@rownum := @rownum + 1 AS num_tmp, "
			+ "@incrnum := CASE "
			+ "WHEN @rowtotal = obj.empiric_value_added THEN "
			+ "@incrnum "
			+ "WHEN @rowtotal := obj.empiric_value_added THEN "
			+ "@rownum "
			+ "END AS rownum "
			+ "FROM "
			+ "( "
			+ "SELECT p1.party_name,p2.empiric_value_added FROM party p1  "
			+ "INNER JOIN promotion_party_added p2 ON p1.id=p2.user_id "
			+ "WHERE p1.party_type = #{type} AND p2.month=#{month} "
			+ "ORDER BY p2.empiric_value_added DESC "
			+ ") AS obj, "
			+ "( "
			+ "SELECT "
			+ "@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 "
			+ ") r "
			+ ") AS obj_new "
			+ "WHERE obj_new.rownum<=3 ")
	Map<String, Object> findRankingAddedThree(Map<String, Object> map) throws Exception;

	@Select(" select * from security_tip_record where user_id = #{userId} and type = #{type} and tip_id = #{tipId} and read_dt = #{readDt}")
	SecurityTipRecord findReadTipRecord(SecurityTipRecord securityTipRecord) throws Exception;

	@Insert(" insert into security_tip_record (user_id,type,tip_id,read_dt) value (#{userId},#{type},#{tipId},#{readDt})")
	@SelectKey(statement = "select last_insert_id()", keyProperty = "id", keyColumn = "id", before = false, resultType = Long.class)
	int insertReadTipRecord(SecurityTipRecord securityTipRecord) throws Exception;

	@Select(" select count(1) total_count from promotion_record where user_id = #{userId} and type = #{type} and added_time like CONCAT('%',#{addedTime},'%') and empiric_item = #{empiricItem}")
	int findAddTimes(Map<String, Object> param) throws Exception;

	@Delete(" delete from security_tip_record where read_dt < DATE_FORMAT(NOW(),'%Y%m%d')")
	void deleteSecurityTipRecord() throws Exception;

	@Select(" select * from elevator_appraisal where SUBSTRING(appraisal_time,1,8) = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 8 DAY),'%Y%m%d') AND is_feed_back=0 ")
	List<ElevatorAppraisal> findFeedbakOvertime() throws Exception;
}
